﻿		--Em sử dụng bảng VPB_LC_DR nhé.

		---	Business_date = ngày làm việc cuối cùng của kỳ báo cáo
		---	Issue_date = thời gian trong kỳ báo cáo (với TYPE = ‘DR’ thì đây là ngày thanh toán, còn TYPE = ‘LC’ thì đây là ngày phát hành)
		---	TYPE = ‘DR’
		---	DR_TYPE in ( ‘SP’,’MA’) – SP là trả ngay, MA là trả chậm
		--	Tính tổng giá trị giao dịch theo cột DOCUMENT_AMOUNT (giá trị chứng từ) và LC_CURRENCY (loại tiền)



		--Phân biệt LC/ nhờ thu xuất nhập khẩu thì em dựa vào 2 ký tự đầu của giá trị trong cột LC_TYPE nhé (xx%%), trong đó:
		---	DE    : nhờ thu XK
		---	LE     : LC XK
		---	DI     : nhờ thu NK
		---	LI      : LC NK

		--Với các loại thanh toán (TYPE = ‘DR’) thì đều cần thêm điều kiện DR_TYPE in (‘MA’,’SP’). 
		--Còn các loại TYPE = ‘LC’ thì lấy full list nhé.


--[TRANGLT9].[LC_BAO_LANH].[dbo].[[TRANGLT9].[LC_BAO_LANH].[dbo].[auto_report_LC]]
--delete  from [TRANGLT9].[LC_BAO_LANH].[dbo].[auto_report_LC]
--where month like'%2015%'
--where branch_code in ('VN0010002','VN0010003','VN0010005','VN0010009','VN0010012')
----RATE***************************
--SELECT save_date,mid_rate FROM SERVER16.[VPB_WHR2].dbo.FOCURR_SAVE
--WHERE save_date in('20151031')
--AND code='usd'
--order by save_date
-----21545.0000
--t7 21165
--t8 21140
--19 21120
--t10 21100
--t11 21110
--t12 21105
--21050.0000--t1/27
--21070.0000--t2/28
--21080.0000--t3/31
--21070.0000-t4/29
--21110.0000--t5/31
--21300.0000--t6/30
--21200.0000--t7/31
--21160.0000--t8/30
--21185.0000--t9/30
--21225.0000-- t10
--21280.0000--11/15
--21350.0000--11/29
--21246.0000--12/31
--21345.0000.0000--01/31
--22480
--drop table [dbo].VPB_customer


--'TAI SAO IT VA OT DEU SU DUNG TAI KHOAN DEBIT_ACCOUNT'??????

--						Bước 1:lay bang vp_customer: WAKE_UP
--						drop table VPB_customer
--						'
--						select * into VPB_customer from server74.bicdata.dbo.customer
--						where segment='SMES'
--						'
--										--bước 2:DP MAST***************************
--										drop table TBL_DP_MAST_TRANG

--										drop table TBL_DP_MAST_TRANG

--										'
--										SELECT CIF,ACCTNO,CUSTOMER_NAME,CUS_OPEN_DATE,SEGMENT,DAO,BRANCH_CODE
--										INTO TBL_DP_MAST_TRANG
--										FROM server74.bicdata.[dbo].[DPTB_MASTER] a
--										where ((segment not in (''KHCN'',''HH'') and a.branch_code not in (select branch_code from anhcp.CEO_PERFORMANCE.dbo.branchoff 
--											 where branch_code <> ''VN0010289''))
--											or (segment is null and LEFT(sector,1)>1))
----'



--SELECT * FROM server74.bicdata.dbo.dp_mast
--WHERE segment='smes'

---SELECT * FROM TBL_DP_MAST_TRANG
----IT************************************
--delete FROM  [TRANGLT9].[LC_BAO_LANH].[dbo].[auto_report_LC]
--delete FROM  engoc.tranglt9.dbo.[TRANGLT9].[LC_BAO_LANH].[dbo].[auto_report_LC] 
--SELECT * FROM engoc.tranglt9.dbo.[TRANGLT9].[LC_BAO_LANH].[dbo].[auto_report_LC]
--SELECT * FROM [TRANGLT9].[LC_BAO_LANH].[dbo].[auto_report_LC]
--SELECT *  FROM [TRANGLT9].[LC_BAO_LANH].[dbo].[auto_report_LC]
 
---- bao cao

--SELECT distinct product_type FROM engoc.tranglt9.dbo.[TRANGLT9].[LC_BAO_LANH].[dbo].[auto_report_LC]

--insert engoc.tranglt9.dbo.[TRANGLT9].[LC_BAO_LANH].[dbo].[auto_report_LC]
--select * FROM [TRANGLT9].[LC_BAO_LANH].[dbo].[auto_report_LC]
--where MONTH='November-2014'

 --where product_type in ('Thanh toan LC nhap',
--'hach toan tien ve LC xuat',
--'Hach toan tien ve nho thu xuat',
--'Thanh toan nho thu nhap')

--select SUM([transaction]) from engoc.tranglt9.dbo.[TRANGLT9].[LC_BAO_LANH].[dbo].[auto_report_LC]
-- where month like 'February-2015%'
--and product_type not in('OT','IT')
 
 
--delete from engoc.tranglt9.dbo.[TRANGLT9].[LC_BAO_LANH].[dbo].[auto_report_LC]
--  where month like 'February-2015%'
  
--  delete from [TRANGLT9].[LC_BAO_LANH].[dbo].[auto_report_LC]
--  where month like 'February-2015%'

 --select * from [TRANGLT9].[LC_BAO_LANH].[dbo].[auto_report_LC]
 -- where month like 'February-2015%'
  
--select Top 10 * from [TRANGLT9].[LC_BAO_LANH].[dbo].[auto_report_LC]

----SELECT * FROM engoc.tranglt9.dbo.[TRANGLT9].[LC_BAO_LANH].[dbo].[auto_report_LC] WHERE zONe_id_sme is NULL
--update engoc.tranglt9.dbo.[TRANGLT9].[LC_BAO_LANH].[dbo].[auto_report_LC]
--SET zone_id_sme='UNALLOCATED' WHERE zONe_id_sme is null



--'Thanh toan LC nhap'
--'hach toan tien ve LC xuat'
--'Hach toan tien ve nho thu xuat'
--'Thanh toan nho thu nhap'

 
declare @sql_IT as varchar(max)
declare @sql_OT as varchar(max)
declare @sql_1 as varchar(max)-- thanh toán LC nhập
declare @sql_2 as varchar(max)--phát hành LC nhập--oki
declare @sql_3 as varchar(max)-- thanh toán nhớ thu nhập
declare @sql_4 as varchar(max)-- xử lý nhờ thu nhập--ok
declare @sql_5 as varchar(max)-- hạch toán tiền về LC xuất
declare @sql_6 as varchar(max)-- Thông báo LC xuất--ok
declare @sql_7 as varchar(max)-- Hạch toán tiền nhờ tthi xuất
declare @sql_8 as varchar(max)-- Xử lý nhờ thu xuất--ok
declare @FROMdt nvarchar (20)
declare @todt nvarchar (20)
declare @rate char(5)
--21320.0000
-- SET @FROMdt='2014-10-01'-- thay ngay dau thang
--SET @todt='2014-10-31'-- thay ngay cuoi thang, tru ngay chu nhat
--SET @rate='21225'-- ty gia o tren

-- SET @FROMdt='2014-01-01'-- thay ngay dau thang
--SET @todt='2014-01-27'-- thay ngay cuoi thang, tru ngay chu nhat
--SET @rate='21050'-- ty gia o tren

-- SET @FROMdt='2014-02-01'-- thay ngay dau thang
--SET @todt='2014-02-28'-- thay ngay cuoi thang, tru ngay chu nhat
--SET @rate='21070'-- ty gia o tren

-- SET @FROMdt='2014-03-01'-- thay ngay dau thang
--SET @todt='2014-03-31'-- thay ngay cuoi thang, tru ngay chu nhat
--SET @rate='21080'-- ty gia o tren

-- SET @FROMdt='2014-04-01'-- thay ngay dau thang
--SET @todt='2014-04-29'-- thay ngay cuoi thang, tru ngay chu nhat
--SET @rate='21070'-- ty gia o tren

-- SET @FROMdt='2014-05-01'-- thay ngay dau thang
--SET @todt='2014-05-31'-- thay ngay cuoi thang, tru ngay chu nhat
--SET @rate='21110'-- ty gia o tren
 
 
--SET @FROMdt='2014-06-01'-- thay ngay dau thang
--SET @todt='2014-06-30'-- thay ngay cuoi thang, tru ngay chu nhat
--SET @rate='21300'-- ty gia o tren

--SET @FROMdt='2014-07-01'-- thay ngay dau thang
--SET @todt='2014-07-31'-- thay ngay cuoi thang, tru ngay chu nhat
--SET @rate='21200'-- ty gia o tren

--SET @FROMdt='2014-08-01'-- thay ngay dau thang
--SET @todt='2014-08-30'-- thay ngay cuoi thang, tru ngay chu nhat
--SET @rate='21160'-- ty gia o tren
								----'
								--SELECT CODE, MID_RATE FROM [SERVER16].[VPB_WHR2].[dbo].[FOCURR_SAVE]
								--WHERE CODE='USD'
								--AND SAVE_DATE='2016-05-31'
								----'

SET @FROMdt='2016-05-01'-- thay ngay dau thang
SET @todt='2016-05-31'-- thay ngay cuoi thang, tru ngay chu nhat
SET @rate='22395'-- ty gia o tren

--save_date	 mid_rate 
--31/12/2014
--31/01/2015	 21,345   
--28/02/2015	 21,360   
--31/03/2015	 21,545   
--27/04/2015	 21,595   
--30/05/2015	 21,810   
--30/06/2015	 21812.5   
--31/07/2015	 21,810   

--t7 21165
--t8 21140
--19 21120

 --21050.0000--t1/27
--21070.0000--t2/28
--21080.0000--t3/31
--21070.0000-t4/29
--21110.0000--t5/31
--21300.0000--t6/30
--21200.0000--t7/31
--21160.0000--t8/30
--21185.0000--t9/30
print @FROMdt
print  @todt
print  @rate
 
 --select MAX(debit_value_date) from [TRANGLT9].[LC_BAO_LANH].[dbo].[auto_report_LC]
 
 --delete  from [TRANGLT9].[LC_BAO_LANH].[dbo].[auto_report_LC]
 --where month='April-2015'
 declare @FROMdt nvarchar (20)
declare @todt nvarchar (20)
declare @rate char(5)

SET @FROMdt='2016-05-01'-- thay ngay dau thang
SET @todt='2016-05-31'-- thay ngay cuoi thang, tru ngay chu nhat
SET @rate='22395'-- ty gia o tren
print @FROMdt
print  @todt
print  @rate
 
 print('1')
INSERT [TRANGLT9].[LC_BAO_LANH].[dbo].[auto_report_LC]
SELECT d.CIF
,d.CUSTOMER_NAME
,d.CUS_OPEN_DATE
,d.SEGMENT
,ft.dao
,ft.co_code
,br.BRANCH_NAME_SME
,br.ZONE_ID_SME
,DEBIT_AMOUNT
,Ft.ID
, DEBIT_VALUE_DATE,INDICATOR=''Xuat khau''
,PRODUCT_NAME=''Chuyen tien ve''
,TRANSACTION_TYPE as PRODCUT_TYPE
,((debit_amount * mid_rate)/'+@rate+')/1e6 as IT_QD
,(DATENAME(mONth,DEBIT_VALUE_DATE)+''-''+DATENAME(YEAR,DEBIT_VALUE_DATE))as MONTH
,ROW_NUMBER() OVER (PARTITION BY d.CIF ORDER BY d.CIF) as COUNT_CIF ---- thay tỷ giá
FROM SERVER16.[VPB_WHR2].[dbo].[FT_HIST] FT
JOIN SERVER16.[VPB_WHR2].dbo.FOCURR_SAVE FS
ON FS.CODE=FT.DEBIT_CCY
   LEFT JOIN TBL_DP_MAST_TRANG d
   ON d.acctno=credit_account
   LEFT JOIN server74.bicdata.dbo.branch_code br
   ON br.branch_id=ft.co_code
  WHERE FT.FT_SECTOR=''Doanh nghiep''
AND co_code not in ('VN0010004', 'VN0010007', 'VN0010008', 'VN0010006', 'VN0010326',  'VN0010327','VN0010335', 'VN0010336', 'VN0010260', 'VN0010001', 'VN0010010','VN0010002','VN0010003','VN0010005','VN0010009','VN0010012')
     AND  DEBIT_VALUE_DATE BETWEEN '2016-05-01' AND '2016-05-31'
  AND TRANSACTION_TYPE=''IT''
  AND SAVE_DATE='2016-05-31'
AND debit_account  not like ('%1300700020001')


exec(@sql_IT)
UPDATE [TRANGLT9].[LC_BAO_LANH].[dbo].[auto_report_LC] SET COUNT_CIF=0 WHERE COUNT_CIF>1
--SELECT * FROM [TRANGLT9].[LC_BAO_LANH].[dbo].[auto_report_LC]
print('2')
SET @sql_OT='
INSERT [TRANGLT9].[LC_BAO_LANH].[dbo].[auto_report_LC]
SELECT d.CIF
,d.CUSTOMER_NAME
,d.CUS_OPEN_DATE
,d.SEGMENT
,ft.DAO
,ft.co_code
,br.BRANCH_NAME_SME
,br.ZONE_ID_SME,
DEBIT_AMOUNT
,ft.ID
, DEBIT_VALUE_DATE,INDICATOR=''Nhap khau''
,PRODUCT_NAME=''Chuyen tien di''
,TRANSACTION_TYPE as PRODCUT_TYPE
,((debit_amount * mid_rate)/'+@rate+')/1e6 as OT_QD
,(DATENAME(mONth,DEBIT_VALUE_DATE)+''-''+DATENAME(YEAR,DEBIT_VALUE_DATE))as MONTH
,ROW_NUMBER() OVER (PARTITION BY d.CIF ORDER BY d.CIF) as COUNT_CIF  -- thay tỷ giá
FROM SERVER16.[VPB_whr2].[dbo].[FT_HIST] FT
    JOIN SERVER16.[VPB_WHR2].dbo.FOCURR_SAVE FS
   ON FS.CODE=FT.DEBIT_CCY
    LEFT JOIN TBL_DP_MAST_TRANG d
   ON d.acctno=DEBIT_ACCOUNT
   LEFT JOIN server74.bicdata.dbo.branch_code br
   ON br.branch_id=ft.co_code
  WHERE FT.FT_SECTOR=''Doanh nghiep''
   AND co_code not in (''VN0010004'', ''VN0010007'', ''VN0010008'', ''VN0010006'', ''VN0010326'',  ''VN0010327'',''VN0010335'', ''VN0010336'', ''VN0010260'', ''VN0010001'', ''VN0010010'',''VN0010002'',''VN0010003'',''VN0010005'',''VN0010009'',''VN0010012'')
     AND  DEBIT_VALUE_DATE BETWEEN '''+@FROMdt+''' AND '''+@todt+'''
  AND TRANSACTION_TYPE=''OT''
  AND SAVE_DATE='''+@todt+''''
 
  exec (@sql_OT)
UPDATE [TRANGLT9].[LC_BAO_LANH].[dbo].[auto_report_LC] SET COUNT_CIF=0 WHERE COUNT_CIF>1
-- SELECT * FROM [TRANGLT9].[LC_BAO_LANH].[dbo].[auto_report_LC]
 
 
--*************************1.thánh toán LC nhập
print('3')
SET @sql_1='
insert [TRANGLT9].[LC_BAO_LANH].[dbo].[auto_report_LC]
  SELECT a.RECID
,a.CUS_NAME
,a.CUS_OPEN_DATE
,a.SEGMENT
,a.DAO
,a.co_code
,a.BRANCH_NAME_SME
,a.ZONE_ID_SME
,a.document_amount,trans_id
,a.ISSUE_DATE,INDICATOR=''Nhap khau''
,PRODUCT_NAME=''LC nhap khau''
,TRANSACTION_TYPE=''Thanh toan LC nhap''
,(a.document_amount*a.mid_rate)/'+@rate+'/1e6 as transactiON_amount
,(DATENAME(mONth,ISSUE_DATE)+''-''+DATENAME(YEAR,ISSUE_DATE))as MONTH
,ROW_NUMBER() OVER (PARTITION BY a.RECID ORDER BY a.RECID) as COUNT_CIF
   FROM (
   SELECT ROW_NUMBER() OVER (PARTITION BY trans_id, ISSUE_DATE ORDER BY ISSUE_DATE desc) RN
,trans_id
,business_date
,document_amount
,d.RECID
,d.cus_name
,d.CUS_OPEN_DATE
,d.SEGMENT
,lc.DAO
,lc.co_code
,b.BRANCH_NAME_SME
,b.ZONE_ID_SME
,ISSUE_DATE
,fs.mid_rate
  FROM server16.[VPB_WHR2].[dbo].[TBL_LC_DR_VPB] lc
  JOIN SERVER16.[VPB_WHR2].dbo.FOCURR_SAVE FS
   ON FS.CODE=lc.LC_CURRENCY
   left join VPB_customer d
ON  lc.APP_CUST_ID=d.RECID
LEFT JOIN server74.bicdata.dbo.branch_code b
ON b.branch_id=lc.co_code
  WHERE ISSUE_DATE BETWEEN '''+@FROMdt+''' AND '''+@todt+'''
  AND TYPE=''DR''
  AND  LC_TYPE like ''LI%''
  and DR_TYPE in (''MA'',''SP'')---------------- thực sự đã phát sinh giao dịch thanh toán
-- and d.segment=''SMES''
   and (lc.segment in (''SMEs'',''CIB'',''CMB'',''KNV'') or lc.segment is null)
   AND co_code not in (''VN0010004'', ''VN0010007'', ''VN0010008'', ''VN0010006'', ''VN0010326'',  ''VN0010327'',''VN0010335'', ''VN0010336'', ''VN0010260'', ''VN0010001'', ''VN0010010'',''VN0010002'',''VN0010003'',''VN0010005'',''VN0010009'',''VN0010012'')
  AND fs.SAVE_DATE='''+@todt+''') A WHERE A.RN=1'
exec(@sql_1)
  UPDATE [TRANGLT9].[LC_BAO_LANH].[dbo].[auto_report_LC] SET COUNT_CIF=0 WHERE COUNT_CIF>1
  
--SELECT * FROM [TRANGLT9].[LC_BAO_LANH].[dbo].[auto_report_LC]
--*************************2.phát hành LC nhập
print('4')
SET @sql_2='
  INSERT [TRANGLT9].[LC_BAO_LANH].[dbo].[auto_report_LC]
  SELECT a.RECID
,a.cus_name
,a.CUS_OPEN_DATE
,a.SEGMENT
,a.DAO
,a.co_code
,a.BRANCH_NAME_SME
,a.ZONE_ID_SME
,a.lc_amount
,a.trans_id
,a.ISSUE_DATE
,INDICATOR=''Nhap khau''
,PRODUCT_NAME=''LC nhap khau'',TRANSACTION_TYPE=''Phat hanh LC nhap''
,(a.lc_amount*a.mid_rate)/'+@rate+'/1e6 as transactiON_amount
,(DATENAME(mONth,ISSUE_DATE)+''-''+DATENAME(YEAR,ISSUE_DATE))as MONTH
,ROW_NUMBER() OVER (PARTITION BY a.RECID ORDER BY a.RECID) as COUNT_CIF 
FROM (
   SELECT ROW_NUMBER() OVER (PARTITION BY trans_id, ISSUE_DATE ORDER BY ISSUE_DATE DESC) RN
,lc_amount
,trans_id
,business_date,document_amount
,d.RECID,d.cus_name
,d.CUS_OPEN_DATE
,d.SEGMENT
,lc.DAO
,lc.co_code
,b.BRANCH_NAME_SME
,b.ZONE_ID_SME
,ISSUE_DATE
,fs.mid_rate
  FROM server16.[VPB_WHR2].[dbo].[TBL_LC_DR_VPB] lc
  JOIN SERVER16.[VPB_WHR2].dbo.FOCURR_SAVE FS
   ON FS.CODE=lc.LC_CURRENCY
      left join VPB_customer d
ON  lc. APP_CUST_ID=d.RECID
LEFT JOIN server74.bicdata.dbo.branch_code b
ON b.branch_id=lc.co_code
  WHERE ISSUE_DATE BETWEEN '''+@FROMdt+''' AND '''+@todt+'''
  AND TYPE=''LC''---
  AND  LC_TYPE like ''LI%'' ---
   --and lc.segment=''SMES''
   and (lc.segment in (''SMEs'',''CIB'',''CMB'',''KNV'') or lc.segment is null)
   AND co_code not in (''VN0010004'', ''VN0010007'', ''VN0010008'', ''VN0010006'', ''VN0010326'',  ''VN0010327'',''VN0010335'', ''VN0010336'', ''VN0010260'', ''VN0010001'', ''VN0010010'',''VN0010002'',''VN0010003'',''VN0010005'',''VN0010009'',''VN0010012'')
  AND fs.SAVE_DATE='''+@todt+''') A WHERE A.RN=1'
 
 exec(@sql_2)
  UPDATE [TRANGLT9].[LC_BAO_LANH].[dbo].[auto_report_LC] SET COUNT_CIF=0 WHERE COUNT_CIF>1
  
--SELECT * FROM [TRANGLT9].[LC_BAO_LANH].[dbo].[auto_report_LC]
  --*************************3.thánh toán nhờ thu nhập
  print('5')
  SET @sql_3='
   INSERT [TRANGLT9].[LC_BAO_LANH].[dbo].[auto_report_LC]
  SELECT a.RECID
,a.cus_name
,a.CUS_OPEN_DATE
,a.SEGMENT
,a.DAO
,a.co_code
,a.BRANCH_NAME_SME
,a.ZONE_ID_SME
,a.document_amount
,a.trans_id
,a.ISSUE_DATE
,INDICATOR=''Nhap khau''
,PRODUCT_NAME=''Nho thu nhap''
,TRANSACTION_TYPE=''Thanh toan nho thu nhap''
,(a.document_amount*a.mid_rate)/'+@rate+'/1e6 as transactiON_amount
,(DATENAME(mONth,ISSUE_DATE)+''-''+DATENAME(YEAR,ISSUE_DATE))as MONTH 
,ROW_NUMBER() OVER (PARTITION BY a.RECID ORDER BY a.RECID) as COUNT_CIF 
FROM (
   SELECT ROW_NUMBER() OVER (PARTITION BY trans_id, ISSUE_DATE ORDER BY ISSUE_DATE DESC) RN
,trans_id
,document_amount
,d.RECID
,d.cus_name
,d.CUS_OPEN_DATE
,d.SEGMENT
,lc.DAO
,lc.co_code
,b.BRANCH_NAME_SME
,b.ZONE_ID_SME
,ISSUE_DATE
,fs.mid_rate
  FROM server16.[VPB_WHR2].[dbo].[TBL_LC_DR_VPB] lc
  JOIN SERVER16.[VPB_WHR2].dbo.FOCURR_SAVE FS
   ON FS.CODE=lc.LC_CURRENCY
    left join VPB_customer d
ON  lc. APP_CUST_ID=d.RECID
LEFT JOIN server74.bicdata.dbo.branch_code b
ON b.branch_id=lc.co_code
  WHERE ISSUE_DATE BETWEEN '''+@FROMdt+''' AND '''+@todt+'''
  AND TYPE=''DR''---
  AND  LC_TYPE like ''DI%'' ---
  and DR_TYPE in (''MA'',''SP'')
   --and lc.segment=''SMES''
   and (lc.segment in (''SMEs'',''CIB'',''CMB'',''KNV'') or lc.segment is null)
   AND co_code not in (''VN0010004'', ''VN0010007'', ''VN0010008'', ''VN0010006'', ''VN0010326'',  ''VN0010327'',''VN0010335'', ''VN0010336'', ''VN0010260'', ''VN0010001'', ''VN0010010'',''VN0010002'',''VN0010003'',''VN0010005'',''VN0010009'',''VN0010012'')
  AND fs.SAVE_DATE='''+@todt+''') A WHERE A.RN=1'
  exec (@sql_3)
   UPDATE [TRANGLT9].[LC_BAO_LANH].[dbo].[auto_report_LC] SET COUNT_CIF=0 WHERE COUNT_CIF>1
--SELECT * FROM [TRANGLT9].[LC_BAO_LANH].[dbo].[auto_report_LC]
----*************************4.xử lý nhờ thu nhập
print('6')
SET @sql_4='
INSERT [TRANGLT9].[LC_BAO_LANH].[dbo].[auto_report_LC]
  SELECT a.RECID
,a.cus_name
,a.CUS_OPEN_DATE
,a.SEGMENT
,a.DAO
,a.co_code
,a.BRANCH_NAME_SME
,a.ZONE_ID_SME
,a.lc_amount
,a.trans_id
,a.ISSUE_DATE
,INDICATOR=''Nhap khau''
,PRODUCT_NAME=''Nho thu nhap''
,TRANSACTION_TYPE=''Xu ly nho thu nhap''
,(a.lc_amount*a.mid_rate)/'+@rate+'/1e6 as transactiON_amount
,(DATENAME(mONth,ISSUE_DATE)+''-''+DATENAME(YEAR,ISSUE_DATE))as MONTH
,ROW_NUMBER() OVER (PARTITION BY a.RECID ORDER BY a.RECID) as COUNT_CIF 
FROM (
   SELECT ROW_NUMBER() OVER (PARTITION BY trans_id, ISSUE_DATE ORDER BY ISSUE_DATE DESC) RN
,trans_id
, business_date
,lc_amount
,d.RECID,d.cus_name
,d.CUS_OPEN_DATE
,d.SEGMENT
,lc.DAO
,lc.co_code
,b.BRANCH_NAME_SME
,b.ZONE_ID_SME
,ISSUE_DATE
,fs.mid_rate
  FROM server16.[VPB_WHR2].[dbo].[TBL_LC_DR_VPB] lc
  JOIN SERVER16.[VPB_WHR2].dbo.FOCURR_SAVE FS
   ON FS.CODE=lc.LC_CURRENCY
   left join VPB_customer d
ON  lc. APP_CUST_ID=d.RECID
LEFT JOIN server74.bicdata.dbo.branch_code b
ON b.branch_id=lc.co_code
  WHERE ISSUE_DATE BETWEEN '''+@FROMdt+''' AND '''+@todt+'''
  AND TYPE=''LC''---
  AND  LC_TYPE like ''DI%'' ---
 --  and lc.segment=''SMES''
   and (lc.segment in (''SMEs'',''CIB'',''CMB'',''KNV'') or lc.segment is null)
  AND co_code not in (''VN0010004'', ''VN0010007'', ''VN0010008'', ''VN0010006'', ''VN0010326'',  ''VN0010327'',''VN0010335'', ''VN0010336'', ''VN0010260'', ''VN0010001'', ''VN0010010'',''VN0010002'',''VN0010003'',''VN0010005'',''VN0010009'',''VN0010012'')
  AND fs.SAVE_DATE='''+@todt+''') A WHERE A.RN=1'
 
  exec(@sql_4)
  UPDATE [TRANGLT9].[LC_BAO_LANH].[dbo].[auto_report_LC] SET COUNT_CIF=0 WHERE COUNT_CIF>1
--SELECT * FROM [TRANGLT9].[LC_BAO_LANH].[dbo].[auto_report_LC]
 
  --*************************5.hạch toán tiền về LC xuất
 print('7')
 SET @sql_5='
  INSERT [TRANGLT9].[LC_BAO_LANH].[dbo].[auto_report_LC]
  SELECT a.RECID
,a.cus_name
,a.CUS_OPEN_DATE
,a.SEGMENT
,a.DAO
,a.co_code
,a.BRANCH_NAME_SME
,a.ZONE_ID_SME
,a.document_amount
,a.trans_id,
a.ISSUE_DATE
,INDICATOR=''Xuat khau''
,PRODUCT_NAME=''LC xuat khau''
,TRANSACTION_TYPE=''hach toan tien ve LC xuat''
,(a.document_amount*a.mid_rate)/'+@rate+'/1e6 as transactiON_amount
,(DATENAME(mONth,ISSUE_DATE)+''-''+DATENAME(YEAR,ISSUE_DATE))as MONTH
,ROW_NUMBER() OVER (PARTITION BY a.RECID ORDER BY a.RECID) as COUNT_CIF 
FROM (
   SELECT ROW_NUMBER() OVER (PARTITION BY trans_id, ISSUE_DATE ORDER BY ISSUE_DATE DESC) RN
,trans_id
,business_date
,document_amount  
,d.RECID
,d.cus_name
,d.CUS_OPEN_DATE
,d.SEGMENT
,lc.DAO
,lc.co_code
,b.BRANCH_NAME_SME
,b.ZONE_ID_SME
,ISSUE_DATE
,fs.mid_rate
     FROM server16.[VPB_WHR2].[dbo].[TBL_LC_DR_VPB] lc
     JOIN SERVER16.[VPB_WHR2].dbo.FOCURR_SAVE FS
     ON FS.CODE=lc.LC_CURRENCY
     left join VPB_customer d
     ON  lc. APP_CUST_ID=d.RECID
     LEFT JOIN server74.bicdata.dbo.branch_code b
     ON b.branch_id=lc.co_code
     WHERE ISSUE_DATE BETWEEN '''+@FROMdt+''' AND '''+@todt+'''
     AND TYPE=''DR''---
      AND  LC_TYPE like ''LE%'' ---
	   and DR_TYPE in (''MA'',''SP'')
      -- and lc.segment=''SMES''
    and (lc.segment in (''SMEs'',''CIB'',''CMB'',''KNV'') or lc.segment is null)
      AND co_code not in (''VN0010004'', ''VN0010007'', ''VN0010008'', ''VN0010006'', ''VN0010326'',  ''VN0010327'',''VN0010335'', ''VN0010336'', ''VN0010260'', ''VN0010001'', ''VN0010010'',''VN0010002'',''VN0010003'',''VN0010005'',''VN0010009'',''VN0010012'')
  AND fs.SAVE_DATE='''+@todt+''') A WHERE A.RN=1'
  exec(@sql_5)
   UPDATE [TRANGLT9].[LC_BAO_LANH].[dbo].[auto_report_LC] SET COUNT_CIF=0 WHERE COUNT_CIF>1
--SELECT * FROM [TRANGLT9].[LC_BAO_LANH].[dbo].[auto_report_LC]
--*************************6.thông báo LC xuất
print('8')
SET @sql_6='
  INSERT [TRANGLT9].[LC_BAO_LANH].[dbo].[auto_report_LC]
  SELECT a.RECID
,a.cus_name
,a.CUS_OPEN_DATE
,a.SEGMENT
,a.DAO
,a.co_code
,a.BRANCH_NAME_SME
,a.ZONE_ID_SME
,a.lc_amount
,a.trans_id
,a.ISSUE_DATE
,INDICATOR=''Xuat khau''
,PRODUCT_NAME=''LC xuat khau''
,TRANSACTION_TYPE=''ThONg bao LC xuat''
,(a.lc_amount*a.mid_rate)/'+@rate+'/1e6 as transactiON_amount,(DATENAME(mONth,ISSUE_DATE)+''-''+DATENAME(YEAR,ISSUE_DATE))as MONTH
,ROW_NUMBER() OVER (PARTITION BY a.RECID ORDER BY a.RECID) as COUNT_CIF 
FROM (
   SELECT ROW_NUMBER() OVER (PARTITION BY trans_id, ISSUE_DATE ORDER BY ISSUE_DATE DESC) RN, 
trans_id
,business_date
,lc_amount
,d.RECID
,d.cus_name
,d.CUS_OPEN_DATE
,d.SEGMENT
,lc.DAO
,lc.co_code
,b.BRANCH_NAME_SME
,b.ZONE_ID_SME
,ISSUE_DATE
,fs.mid_rate
     FROM server16.[VPB_WHR2].[dbo].[TBL_LC_DR_VPB] lc
     JOIN SERVER16.[VPB_WHR2].dbo.FOCURR_SAVE FS
     ON FS.CODE=lc.LC_CURRENCY
     left join VPB_customer d
     ON  lc. APP_CUST_ID=d.RECID
      LEFT JOIN server74.bicdata.dbo.branch_code b
   ON b.branch_id=lc.co_code
  WHERE ISSUE_DATE BETWEEN '''+@FROMdt+''' AND '''+@todt+'''
  AND TYPE=''LC''---
  AND  LC_TYPE like ''LE%'' ---
  -- and lc.segment=''SMES''
   and (lc.segment in (''SMEs'',''CIB'',''CMB'',''KNV'') or lc.segment is null)
   AND co_code not in (''VN0010004'', ''VN0010007'', ''VN0010008'', ''VN0010006'', ''VN0010326'',  ''VN0010327'',''VN0010335'', ''VN0010336'', ''VN0010260'', ''VN0010001'', ''VN0010010'',''VN0010002'',''VN0010003'',''VN0010005'',''VN0010009'',''VN0010012'')
  AND fs.SAVE_DATE='''+@todt+''') A WHERE A.RN=1'
 
  exec(@sql_6)
   UPDATE [TRANGLT9].[LC_BAO_LANH].[dbo].[auto_report_LC] SET COUNT_CIF=0 WHERE COUNT_CIF>1
-- SELECT * FROM [TRANGLT9].[LC_BAO_LANH].[dbo].[auto_report_LC]
  --*************************7.hạch toán toeefn về nhờ thu xuất
  print('9')
  SET @sql_7='
   INSERT [TRANGLT9].[LC_BAO_LANH].[dbo].[auto_report_LC]
  SELECT a.RECID
,a.cus_name
,a.CUS_OPEN_DATE
,a.SEGMENT
,a.DAO
,a.co_code
,a.BRANCH_NAME_SME
,a.ZONE_ID_SME
,a.document_amount
,a.trans_id
,a.ISSUE_DATE,INDICATOR=''Xuat khau''
,PRODUCT_NAME=''Nho thu xuat''
,TRANSACTION_TYPE=''Hach toan tien ve nho thu xuat''
,(a.document_amount*a.mid_rate)/'+@rate+'/1e6 as transactiON_amount
,(DATENAME(mONth,ISSUE_DATE)+''-''+DATENAME(YEAR,ISSUE_DATE))as MONTH
,ROW_NUMBER() OVER (PARTITION BY a.RECID ORDER BY a.RECID) as COUNT_CIF 
FROM (
   SELECT ROW_NUMBER() OVER (PARTITION BY trans_id, ISSUE_DATE ORDER BY ISSUE_DATE DESC) RN
, trans_id
, business_date
,document_amount  
,d.RECID
,d.cus_name
,d.CUS_OPEN_DATE
,d.SEGMENT
,lc.DAO
,lc.co_code
,b.BRANCH_NAME_SME
,b.ZONE_ID_SME
,ISSUE_DATE,fs.mid_rate
  FROM server16.[VPB_WHR2].[dbo].[TBL_LC_DR_VPB] lc
  JOIN SERVER16.[VPB_WHR2].dbo.FOCURR_SAVE FS
   ON FS.CODE=lc.LC_CURRENCY
    left join VPB_customer d
ON  lc. APP_CUST_ID=d.RECID
LEFT JOIN server74.bicdata.dbo.branch_code b
ON b.branch_id=lc.co_code
  WHERE ISSUE_DATE BETWEEN '''+@FROMdt+''' AND '''+@todt+'''
  AND TYPE=''DR''---
  AND  LC_TYPE like ''DE%'' ---
  and DR_TYPE in (''MA'',''SP'')
  -- and lc.segment=''SMES''
   and (lc.segment in (''SMEs'',''CIB'',''CMB'',''KNV'') or lc.segment is null)
AND co_code not in (''VN0010004'', ''VN0010007'', ''VN0010008'', ''VN0010006'', ''VN0010326'',  ''VN0010327'',''VN0010335'', ''VN0010336'', ''VN0010260'', ''VN0010001'', ''VN0010010'',''VN0010002'',''VN0010003'',''VN0010005'',''VN0010009'',''VN0010012'')
  AND fs.SAVE_DATE='''+@todt+''') A WHERE A.RN=1'
  exec (@sql_7)
   UPDATE [TRANGLT9].[LC_BAO_LANH].[dbo].[auto_report_LC] SET COUNT_CIF=0 WHERE COUNT_CIF>1
 --SELECT * FROM [TRANGLT9].[LC_BAO_LANH].[dbo].[auto_report_LC]
--*************************8. xử lý nhờ thu xuất
print('10')
SET @sql_8='
  INSERT [TRANGLT9].[LC_BAO_LANH].[dbo].[auto_report_LC]
  SELECT a.RECID
,a.cus_name
,a.CUS_OPEN_DATE
,a.SEGMENT
,a.DAO
,a.co_code
,a.BRANCH_NAME_SME
,a.ZONE_ID_SME
,a.lc_amount
,a.trans_id
,a.ISSUE_DATE
,INDICATOR=''Xuat khau''
,PRODUCT_NAME=''Nho thu xuat''
,TRANSACTION_TYPE=''Xu ly nho thu xuat''
,(a.lc_amount*a.mid_rate)/'+@rate+'/1e6 as transactiON_amount
,(DATENAME(mONth,ISSUE_DATE)+''-''+DATENAME(YEAR,ISSUE_DATE))as MONTH
,ROW_NUMBER() OVER (PARTITION BY a.RECID ORDER BY a.RECID) as COUNT_CIF 
FROM (
   SELECT ROW_NUMBER() OVER (PARTITION BY trans_id, ISSUE_DATE ORDER BY ISSUE_DATE DESC) RN, 
trans_id
, business_date
,lc_amount
 ,d.RECID
,d.cus_name
,d.CUS_OPEN_DATE
,d.SEGMENT
,lc.DAO
,lc.co_code
,b.BRANCH_NAME_SME
,b.ZONE_ID_SME
,ISSUE_DATE
,fs.mid_rate
  FROM server16.[VPB_WHR2].[dbo].[TBL_LC_DR_VPB] lc
  JOIN SERVER16.[VPB_WHR2].dbo.FOCURR_SAVE FS
   ON FS.CODE=lc.LC_CURRENCY
    left join VPB_customer d
ON  lc. APP_CUST_ID=d.RECID
LEFT JOIN server74.bicdata.dbo.branch_code b
ON b.branch_id=lc.co_code
  WHERE ISSUE_DATE BETWEEN'''+@FROMdt+''' AND '''+@todt+'''
  AND TYPE=''LC''---
  AND  LC_TYPE like ''DE%'' ---
   --and lc.segment=''SMES''
   and (lc.segment in (''SMEs'',''CIB'',''CMB'',''KNV'') or lc.segment is null)
  AND co_code not in (''VN0010004'', ''VN0010007'', ''VN0010008'', ''VN0010006'', ''VN0010326'',  ''VN0010327'',''VN0010335'', ''VN0010336'', ''VN0010260'', ''VN0010001'', ''VN0010010'',''VN0010002'',''VN0010003'',''VN0010005'',''VN0010009'',''VN0010012'')
  AND fs.SAVE_DATE='''+@todt+''') A WHERE A.RN=1'
 
  exec(@sql_8)
   UPDATE [TRANGLT9].[LC_BAO_LANH].[dbo].[auto_report_LC] SET COUNT_CIF=0 WHERE COUNT_CIF>1
   


-------------chạy tiếp


--SELECT distinct month FROM [TRANGLT9].[LC_BAO_LANH].[dbo].[auto_report_LC] 

			insert engoc.tranglt9.dbo.[auto_report_LC]
			SELECT * FROM [TRANGLT9].[LC_BAO_LANH].[dbo].[auto_report_LC] 
			--where month ='September-2015'
			--where month ='October-2015'
			--where month ='January-2016'
			--where month ='February-2016'
			--where month ='March-2016'
			--where month ='April-2016'
			where month='May-2016'


		INSERT  SERVER12.ANHCP.DBO.[DOANH_SO_TTR_LC_DP] 
		SELECT *, LOAI_GIAO_DICH =CASE WHEN PRODUCT_TYPE IN ('IT', 'OT') THEN 'TTR'
					WHEN PRODUCT_TYPE LIKE '%LC%' THEN 'LC'
					WHEN PRODUCT_TYPE LIKE '%NHO THU%' THEN 'NHO THU'
					END,
					YEAR_MONTH='201605', amt_bill=0
					 FROM [TRANGLT9].[LC_BAO_LANH].[dbo].[auto_report_LC] 
				--where month ='September-2015'
				--where month ='October-2015'
				--where month ='January-2016'
				--where month ='February-2016'
				--where month ='March-2016'
				--where month ='April-2016'
				where month='May-2016'


				select *,  CONVERT(VARCHAR, LEFT (CONVERT(VARCHAR, SAVE_DATE, 112), 4)
					+SUBSTRING (CONVERT(VARCHAR, SAVE_DATE, 112), 5, 2)
			+RIGHT (CONVERT(VARCHAR, SAVE_DATE, 112),2),8) as DATE_QD INTO  ##A
				 from [SERVER16].[VPB_WHR2].[dbo].[FOCURR_SAVE] where code='USD'
				 and  SAVE_DATE IN (
				 --'20150131','20150228', '20150331','20150427', '20150530','20150630', 
				 --'20150731', '20150831', '20150930', '20151031','20151130', '20151231', 
				 --'20160130', '20160229', '20160331', '20160429',
				 '20160531')


				update a
				set a.amt_bill=a.[[transaction]]] * b.mid_rate/1e3
				from  SERVER12.ANHCP.DBO.[DOANH_SO_TTR_LC_DP]  a,
				 ##A B
				where A.YEAR_MONTH= LEFT(B.DATE_QD, 6)
				and YEAR_MONTH='201605'

				
		--update vung khong xac dinh
		
		update engoc.tranglt9.[dbo].[auto_report_LC]
		set ZONE_ID_SME='UNALLOCATED'
		where zone_id_sme is null
		


'







--delete from  [TRANGLT9].[LC_BAO_LANH].[dbo].[auto_report_LC]
--where month like 'MAY-2015%'

--delete from  engoc.tranglt9.[dbo].[auto_report_LC]
--where month like 'October-2015'


--select  distinct month from  [TRANGLT9].[LC_BAO_LANH].[dbo].[auto_report_LC]
--where month like 'August-2015%'

--delete from  [TRANGLT9].[LC_BAO_LANH].[dbo].[auto_report_LC]
--where month = 'October-2015'


--select * from engoc.tranglt9.dbo.[auto_report_LC]  where month ='September-2015'



			--SELECT distinct month FROM [TRANGLT9].[LC_BAO_LANH].[dbo].[auto_report_LC] order by month desc



--select * from SERVER12.ANHCP.DBO.[DOANH_SO_TTR_LC_DP]

--SELECT *,
--LOAI_GIAO_DICH =CASE WHEN PRODUCT_TYPE IN ('IT', 'OT') THEN 'TTR'
--WHEN PRODUCT_TYPE LIKE '%LC%' THEN 'LC'
--WHEN PRODUCT_TYPE LIKE '%NHO THU%' THEN 'NHO THU'
--END 

--select min(debit_value_date) from engoc.tranglt9.dbo.[auto_report_LC]
--select * from engoc.tranglt9.dbo.[auto_report_LC]





--delete from engoc.tranglt9.dbo.[auto_report_LC]
--where month ='October-2015'

--SELECT distinct month FROM [TRANGLT9].[LC_BAO_LANH].[dbo].[auto_report_LC]
--order by month

--SELECT distinct month FROM [TRANGLT9].[LC_BAO_LANH].[dbo].[auto_report_LC]
--where month like 'MAY-2015%'


--update [DOANH_SO_TTR_LC_DP]
--set year_month='201408'
--where month ='August-2014'

--update [DOANH_SO_TTR_LC_DP]
--set year_month='201601'
----where month ='June-2015'
----where month ='July-2015'
----where month ='September-2015'
----where month ='September-2014'
----where month ='February-2014'
----where month='April-2015'
--where month='January-2016'
----where month ='August-2015'
----where month ='November-2015'
----where month ='April-2014'
----where month ='December-2015'
----where month ='May-2015'
----where month ='July-2014'
----where month ='November-2014'
----where month ='October-2015'
----where month ='March-2015'
----where month ='March-2016'
----where month ='June-2014'
----where month ='October-2014'
----where month ='January-2015'
----where month ='January-2014'
----where month ='February-2016'
----where month ='May-2014'
----where month ='March-2014'
----where month ='February-2015'
----where month ='December-2014'






--update engoc.tranglt9.dbo.[TRANGLT9].[LC_BAO_LANH].[dbo].[auto_report_LC]
--set ZONE_ID_SME='UNALLOCATED'
--where zone_id_sme=''

--select * from engoc.tranglt9.dbo.[TRANGLT9].[LC_BAO_LANH].[dbo].[auto_report_LC]
--where zone_id_sme=''


--update engoc.tranglt9.dbo.[TRANGLT9].[LC_BAO_LANH].[dbo].[auto_report_LC]
--set zone_ID_SME='VUNG 06'
--where MONTH='January-2015'
--and branch_name_Sme='VUNG TAU'

--------------
--SELECT INDICATOR,PRODUCT_NAME,PRODUCT_TYPE,SUM([TRANSACTION]) as TRANS_AMT  
--FROM  ENGOC.TRANGLT9.DBO.[TRANGLT9].[LC_BAO_LANH].[dbo].[auto_report_LC]
--WHERE MONTH LIKE'%2015%'
--and PRODUCT_NAME not in ('Chuyen tien di','Chuyen tien ve')
--GROUP BY  INDICATOR,PRODUCT_NAME,PRODUCT_TYPE

--select distinct cif 
--into #a from engoc.tranglt9.dbo.[TRANGLT9].[LC_BAO_LANH].[dbo].[auto_report_LC]
--where year(cus_open_date)='2015'

--select * FROM ENGOC.TRANGLT9.DBO.[TRANGLT9].[LC_BAO_LANH].[dbo].[auto_report_LC]

--SELECT  distinct CIF, ZONE_ID_SME, BRANCH_NAME_SME   FROM ENGOC.TRANGLT9.DBO.[TRANGLT9].[LC_BAO_LANH].[dbo].[auto_report_LC]
--WHERE CIF IN (SELECT CIF FROM #A)

--SELECT a.CIF, c.ZONE_ID_SME, c.BRANCH_NAME_SME FROM #A a
--JOIN [SERVER74].[BICDATA].[DBO].[CUSTOMER] B
--ON A.CIF=B.RECID
--join  [SERVER74].[BICDATA].[dbo].[BRANCH_CODE] c
--on b.company_book =c.branch_id
